package org.zjvis.datascience.common.sql;

import static org.zjvis.datascience.common.util.ToolUtil.checkDuplicateName;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.zjvis.datascience.common.constant.DatasetConstant;
import org.zjvis.datascience.common.constant.IdConstant;
import org.zjvis.datascience.common.constant.SemanticConstant;
import org.zjvis.datascience.common.constant.SqlTemplate;
import org.zjvis.datascience.common.enums.ActionEnum;
import org.zjvis.datascience.common.enums.DataTypeEnum;
import org.zjvis.datascience.common.enums.PythonDateTypeFormatEnum;
import org.zjvis.datascience.common.enums.SemanticSubEnum;
import org.zjvis.datascience.common.exception.FormulaException;
import org.zjvis.datascience.common.exception.InvalidFormulaException;
import org.zjvis.datascience.common.model.CompositeConfig;
import org.zjvis.datascience.common.model.ConfigComponent;
import org.zjvis.datascience.common.model.DimensionConfig;
import org.zjvis.datascience.common.model.stat.ColumnConstant;
import org.zjvis.datascience.common.strategy.ValueHandlingContext;
import org.zjvis.datascience.common.util.CollectionUtil;
import org.zjvis.datascience.common.util.DatasetUtil;
import org.zjvis.datascience.common.util.SemanticUtil;
import org.zjvis.datascience.common.util.SqlUtil;
import org.zjvis.datascience.common.util.TimeUtil;
import org.zjvis.datascience.common.util.ToolUtil;

/**
 * @description : 数据清洗CLEAN系统帮助类
 * @date 2021-12-23
 */
public class DataCleanSqlHelper {

    private final static Logger logger = LoggerFactory.getLogger(DataCleanSqlHelper.class);

    private final Map<String, Integer> columnTypes;
    private final JSONObject data;
    private final String sourceTable;
    private String targetTable;

    public DataCleanSqlHelper(Map<String, Integer> columnTypes, JSONObject data,
        String sourceTable, String targetTable) {
        this.columnTypes = columnTypes;
        this.data = data;
        this.sourceTable = sourceTable;
        this.targetTable = targetTable;
    }

    // view_tclean_(taskId)_(timestamp)
    public static String VIEW_TABLE_NAME = SqlTemplate.SCHEMA + ".view_tclean_%s_%s";
    public static String SOLID_TABLE_NAME = SqlTemplate.SCHEMA + ".solid_tclean_%s_%s";

    public static String SELECT_SQL = "SELECT %s FROM %s ";
    public static String WITH_SQL = "WITH %s AS ( %s ) %s";
    public static String IDCOLUMN_NAME = "_record_id_";
    public static String ROW_NUM_COMBINATION = "row_number( ) over ( %s ) AS %s";

    public static String RENAME_SQL = "\"%s\" as \"%s\"";
    public static String TRANSFORM_SQL = "CAST(%s as %s)";
    public static String GROUP_SQL = "CASE WHEN %s THEN %s ELSE \"%s\" END";
    public static String MERGE_SQL = "CONCAT(%s) as \"%s\"";

    public static String UPPER_SQL = "upper(\"%s\") as \"%s\"";
    public static String LOWER_SQL = "lower(\"%s\") as \"%s\"";
    public static String REMOVE_ALPHA_SQL = "regexp_replace(\"%s\"::varchar, '[[:alpha:]]', '', 'g') as \"%s\"";
    public static String REMOVE_DIGIT_SQL = "regexp_replace(\"%s\"::varchar, '[[:digit:]]', '', 'g') as \"%s\"";
    public static String REMOVE_PUNCT_SQL = "regexp_replace(\"%s\"::varchar, '[[:punct:]，。/；‘’【】、·《》？：“”{}|~！@#￥…&*（）——+]', '', 'g') as \"%s\"";
    public static String TRIM_SQL = "btrim(\"%s\") as \"%s\"";
    public static String REPLACE_SQL = "replace(\"%s\", ' ', '') as \"%s\"";

    public static String DATE_YEAR_SQL = "CAST(EXTRACT(YEAR FROM \"%s\") as integer) as \"%s\"";
    public static String DATE_MONTH_SQL = "CAST(EXTRACT(MONTH FROM \"%s\") as integer) as \"%s\"";
    public static String DATE_DAY_SQL = "CAST(EXTRACT(DAY FROM \"%s\") as integer) as \"%s\"";
    public static String DATE_WEEK_SQL = "CAST(EXTRACT(WEEK FROM \"%s\") as integer) as \"%s\"";
    public static String DATE_QUARTERLY_SQL = "CAST(TO_CHAR(\"%s\", 'Q') as integer) as \"%s\"";

    public static String SPLIT_FIRST_SQL = "split_part(\"%s\", '%s', %d) as %s";
    public static String SPLIT_FIRST_FULL_SQL = "select %s,* from %s";
    public static String SPLIT_LAST_SQL =
        "(regexp_split_to_array(\"%s\", '%s')::text[])[ array_length((regexp_split_to_array(\"%s\", '%s')::text[]), 1)+1-%d ] "
            + "as %s";
    public static String MAX_SPLIT_SQL =
        "select max(array_length(regexp_split_to_array(\"%s\",'%s'),1)) "
            + "from (select \"%s\" from %s limit 10000) t";

    public static String TRANSFORM_SET_SQL = "case when %s then %s else %s end as %s";
    public static String TRANSFORM_REMOVE_SQL = "(select row_number() over(partition by %s %s) as \"_row_num_\",* from %s) a where \"_row_num_\" = 1";
    public static String TRANSFORM_REPLACE_SQL = "btrim(regexp_replace(%s::varchar, E'%s', '%s' %s)) as %s";
    public static String REPLACE_POSITION_SQL = "(overlay(%s placing '%s' from %d for %d))";
    public static String WORD_POSITION_REPLACE = "case when split_part(%s, ' ', %d) = '' then %s else replace(%s, split_part(%s, ' ', %d) , '%s') end as %s";

    public static String GROUP_BY_TYPE1_INNER = "(select %s from %s b where %s) as %s";
    public static String GROUP_BY_TYPE2 = "select min(_record_id_) as _record_id_, %s, %s as %s from %s group by %s";
    public static String GROUP_BY_EMPTY_TYPE1_INNER = "(select %s from %s) as %s";
    public static String GROUP_BY_EMPTY_TYPE2 = "select min(_record_id_) as _record_id_, %s as %s from %s";

    public static String PARTITION_TEMPLATE_NAME = "top_N_template";
    public static int MAX_NESTING_LEVEL = 5;

    public static String PERCENTAGE_FILTER_SQL = "select * from %s where %s is not null %s ";

    public static String PERCENTAGE_FILTER_INCLUDE_NULL_SQL = "select * from (%s) a union all select * from %s where %s is null";

    public static Set<String> functionSet = new HashSet<String>() {
        {
            add("sum");
            add("average");
            add("max");
            add("min");
            add("if");
            add("number");
            add("power");
            add("sqrt");
            add("log");
            add("abs");
            add("floor");
            add("round");
            add("ceiling");
            add("int");
            add("date");
            add("time");
            add("now");
            add("timeadd");
            add("string");
            add("concat");
            add("replace");
            add("aggr_max");
            add("aggr_min");
            add("aggr_average");
            add("aggr_sum");
        }
    };

    public static Set<String> widgetFunctionSet = new HashSet<String>() {
        {
            add("aggr_max");
            add("aggr_min");
            add("aggr_average");
            add("aggr_sum");
        }
    };

    public static Set<String> specialStrings = new HashSet<String>() {
        {
            add("Math.PI");
            add("Math.E");
            add("true");
            add("false");
        }
    };

    public static String initSql(Map<String, Integer> columnTypes, JSONObject data, Long taskId) {
        //清洗的类型
        String action = data.getString("action");
        ActionEnum actionEnum = ActionEnum.valueOf(action);
        data.put("label", actionEnum.label());
        String sourceTable = ToolUtil.alignTableName(data.getString("table"), 0L);
        String targetTable;
        switch (actionEnum) {
            case GROUP_BY:
                targetTable = String.format(SOLID_TABLE_NAME, taskId, System.currentTimeMillis());
                break;
            default:
                targetTable = String.format(VIEW_TABLE_NAME, taskId, System.currentTimeMillis());
        }

        if (ActionEnum.ORIGINAL_DATA == actionEnum) {
            data.put("table", sourceTable);
            return "";
        }
        if (CollectionUtil.isEmpty(columnTypes)) {
            data.put("table", targetTable);
            throw new RuntimeException("table " + sourceTable + " not exits");
        }

        String sql = "";
        DataCleanSqlHelper sqlHelper = new DataCleanSqlHelper(
                Maps.newLinkedHashMap(columnTypes), data, sourceTable, targetTable);
        switch (actionEnum) {
            case RENAME:
                sql = sqlHelper.actionRename();
                break;
            case TYPE_TRANSFORM:
                sql = sqlHelper.actionTransform();
                break;
            case FORMAT_TRANSFORM:
                sql = sqlHelper.formatTransform();
                break;
            case EDIT_ORDER:
                sql = sqlHelper.editOrder();
                break;
            case GROUP:
            case REPLACE:
                sql = sqlHelper.actionGroup();
                break;
            case SPLIT:
                sql = sqlHelper.actionColumnSplit();
                break;
            case JSON_PARSE:
                sql = sqlHelper.jsonParse();
                break;
            case ARRAY_PARSE:
                sql = sqlHelper.arrayParse();
                break;
            case COPY:
                sql = sqlHelper.actionColumnCopy();
                break;
            case REMOVE:
                sql = sqlHelper.actionColumnRemove();
                break;
            case RETAIN:
                sql = sqlHelper.actionColumnRetain();
                break;
            case MERGE:
                sql = sqlHelper.actionColumnMerge();
                break;
            case JSON_MERGE:
                sql = sqlHelper.jsonMerge();
                break;
            case ARRAY_MERGE:
                sql = sqlHelper.arrayMerge();
                break;
            //筛选器
            case FILTER:
                JSONObject filter = data.getJSONArray("filter").getJSONArray(0).getJSONObject(0);
                String filterType = filter.getString("filterType");
                switch (filterType) {
                    case "a%-b%":
                    case "0%-b%":
                    case "a%-100%":
                        sql = sqlHelper.actionPercentageFilter();
                        break;
                    default:
                        sql = sqlHelper.actionFilter();
                        break;
                }
                break;
            //字符串清理
            case PROCESS_UPPERCASE:
                sql = sqlHelper.actionCleanUp(UPPER_SQL);
                break;
            case PROCESS_LOWERCASE:
                sql = sqlHelper.actionCleanUp(LOWER_SQL);
                break;
            case REMOVE_ALPHA:
                sql = sqlHelper.actionCleanUp(REMOVE_ALPHA_SQL);
                break;
            case REMOVE_DIGITAL:
                sql = sqlHelper.actionCleanUp(REMOVE_DIGIT_SQL);
                break;
            case REMOVE_PUNCT:
                sql = sqlHelper.actionCleanUp(REMOVE_PUNCT_SQL);
                break;
            case PROCESS_TRIM:
                sql = sqlHelper.actionCleanUp(TRIM_SQL);
                break;
            case REMOVE_SPACE:
                sql = sqlHelper.actionCleanUp(REPLACE_SQL);
                break;
            //日期转换
            case DATE_YEAR:
                sql = sqlHelper.actionCleanUp(DATE_YEAR_SQL);
                break;
            case DATE_MONTH:
                sql = sqlHelper.actionCleanUp(DATE_MONTH_SQL);
                break;
            case DATE_DAY:
                sql = sqlHelper.actionCleanUp(DATE_DAY_SQL);
                break;
            case DATE_WEEK:
                sql = sqlHelper.actionCleanUp(DATE_WEEK_SQL);
                break;
            case DATE_QUARTERLY:
                sql = sqlHelper.actionCleanUp(DATE_QUARTERLY_SQL);
                break;
            case TRANSFORM_SET:
                sql = sqlHelper.transformSet();
                break;
            case TRANSFORM_REMOVE:
                sql = sqlHelper.transformRemove();
                break;
            case TRANSFORM_REPLACE:
                sql = sqlHelper.transformReplace();
                break;
            case TRANSFORM_EXTRACT:
                sql = sqlHelper.transformExtract();
                break;
            case TRANSFORM_SPLIT:
                sql = sqlHelper.transformSplit();
                break;
            case TRANSFORM_DELETE:
                sql = sqlHelper.transformDeleteOrKeep(ActionEnum.TRANSFORM_DELETE);
                break;
            case TRANSFORM_KEEP:
                sql = sqlHelper.transformDeleteOrKeep(ActionEnum.TRANSFORM_KEEP);
                break;
            case RESULT_EXTRACT:
                sql = sqlHelper.resultExtract();
                break;
            case GROUP_BY:
                sql = sqlHelper.actionGroupBy();
                break;
            case ADD_COLUMN_BASED_ON_FORMULA:
                sql = sqlHelper.addColumnOnFormula(false);
                break;
            case PARTITION_AND_ORDER:
                sql = sqlHelper.actionPartitionAndOrder();
                break;
            case NUMBER_FORMAT_CONVERSION:
                sql = sqlHelper.actionNumberFormatConversion();
                break;
        }
        data.put("table", sqlHelper.targetTable);
        return sql;
    }

    private String actionPercentageFilter() {
        JSONObject filter = data.getJSONArray("filter").getJSONArray(0).getJSONObject(0);
        String col = filter.getString("col");
        String filterType = filter.getString("filterType");
        boolean includeNull = filter.getBooleanValue("includeNull");
        if (!columnTypes.containsKey(col)) {
            data.put("table", targetTable);
            throw new RuntimeException("column " + col + " dose not exist in " + sourceTable);
        }
        col = SqlUtil.formatPGSqlColName(filter.getString("col"));

        String orderBy = "";
        JSONArray values = filter.getJSONArray("values");
        long v0 = values.getLongValue(0);
        long v1 = values.getLongValue(1);
        switch (filterType) {
            case "a%-b%":
                if (v0==-1) {
                    orderBy = " order by " + col + " offset " + v1;
                    break;
                }
                orderBy = " order by " + col + " limit "+ v0 +" offset " + v1;
                break;
            case "0%-b%":
                orderBy = " order by " + col + " limit "+ v0;
                break;
            case "a%-100%":
                orderBy = " order by " + col + " offset " + v1;
                break;
            default:
                return "";
        }
        String sql = String.format(PERCENTAGE_FILTER_SQL, sourceTable, col, orderBy);
        if (includeNull) {
            sql = String.format(PERCENTAGE_FILTER_INCLUDE_NULL_SQL, sql, sourceTable, col);
        }

        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, sql);
    }

    /**
     * TopN 问题 CREATE VIEW pipeline.ayayatest3 AS WITH top_N_template AS ( SELECT column_1,
     * column_2, column_3, column_4, column_5, ROW_NUMBER ( ) OVER ( PARTITION BY column_4 ORDER BY
     * column_2 DESC ) AS col_2_rank FROM dataset.x_249_1630917532256 ) SELECT ROW_NUMBER ( ) OVER (
     * ) AS _record_id_,
     * <p>
     * FROM top_N_template WHERE col_2_rank <= 3 ORDER BY _record_id_
     *
     * @return
     */
    private String actionPartitionAndOrder() {
        List<String> groupByCols = data.getJSONArray("groupByCols").toJavaList(String.class);
        String sortByCol = data.getString("sortByCol");
        String order = data.getString("orderByPolicy");
        String newCol = data.getString("newColumn");
        String currentCol = data.getString("col");
        Set<String> originalColsSet = columnTypes.keySet();
        newCol = checkDuplicateName(newCol, new ArrayList<String>(originalColsSet));
        //如果newCol 已经存在于originalColSet, 则需要改名
        originalColsSet.remove("_record_id_");
        List<String> cols = SqlUtil.formatPGSqlCols(Lists.newLinkedList(originalColsSet));

        if (columnTypes.get(sortByCol) == Types.DATE){
            sortByCol = String.format("pipeline.sys_func_format_time(\"%s\"::varchar, '%s')", sortByCol, PythonDateTypeFormatEnum.FORMAT_0.getVal());
        } else {
            sortByCol = SqlUtil.formatPGSqlColName(sortByCol);
        }
        newCol = wrapGroupBy(groupByCols, sortByCol, newCol, order);
        String selectSql = String
            .format(SPLIT_FIRST_FULL_SQL,
                String.format(ROW_NUM_COMBINATION, "", "\"" + IDCOLUMN_NAME + "\""),
                PARTITION_TEMPLATE_NAME);
        //插入在原始字段的附近，保证他们在相邻的位置
        cols.add(cols.indexOf("\"" + currentCol + "\"") + 1, newCol);
        String columnSql = buildSelectSql(cols.iterator(), sourceTable);
        String withSql = buildWithSql(PARTITION_TEMPLATE_NAME, columnSql, selectSql);
        logger.info("SQL generated from actionPartitionAndOrder : {}", withSql);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, withSql);
    }

    private String wrapGroupBy(List<String> groupByCols, String sortByCol,  String newCol, String order) {
        String groupByPart = "\"" + Joiner.on("\", \"").join(groupByCols) + "\"";
        String partitionBy = String.format("PARTITION BY %s ORDER BY %s %s", groupByPart, sortByCol, order);
        return String.format(ROW_NUM_COMBINATION, partitionBy, "\"" +newCol + "\"");
    }

    public static String initAddColumnBasedOnFormulaSql(Map<String, Integer> columnTypes,
        JSONObject data, Long taskId, boolean edit) {
        String action = data.getString("action");
        ActionEnum actionEnum = ActionEnum.valueOf(action);
//        data.put("label", actionEnum.label());
        String sourceTable = ToolUtil.alignTableName(data.getString("table"), 0L);
        String targetTable = String.format(VIEW_TABLE_NAME, taskId, System.currentTimeMillis());
        if (CollectionUtil.isEmpty(columnTypes)) {
//            data.put("table", targetTable);
            throw new RuntimeException("table " + sourceTable + " not exits");
        }
        DataCleanSqlHelper sqlHelper = new DataCleanSqlHelper(Maps.newLinkedHashMap(columnTypes),
            data, sourceTable,
            targetTable);
        String sql = sqlHelper.getAddColumnOnFormulaSelectSql(edit);
        logger.info("select sql: " + sql);
//        data.put("table", sqlHelper.targetTable);
        return sql;
    }

    /**
     * 根据结果推荐清洗
     */
    private String resultExtract() {
        String transformType = data.getString("transformType");
        String originalCol = data.getString(ColumnConstant.COL);
        String col = SqlUtil.formatPGSqlColName(originalCol);
        String newCol = SqlUtil.formatPGSqlColName(data.getString(ColumnConstant.NEW_COL));
        String extract;
        switch (transformType) {
            case ColumnConstant.INT_REPEAT:
                int repeatNum = data.getIntValue(ColumnConstant.REPEAT_NUM);
                extract = String
                    .format("cast(repeat(cast(%s as text) , %d) as int8)", col, repeatNum);
                break;
            case ColumnConstant.STRING_FILLING:
                String prefix = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.PREFIX));
                String suffix = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.SUFFIX));
                extract = String.format("concat('%s', %s, '%s')", prefix, col, suffix);
                break;
            case ColumnConstant.POLYNOMIAL:
                extract = polynomialExtract(col);
                break;
            case ColumnConstant.DECIMAL_POINT_MOVE:
                int moveNum = data.getIntValue(ColumnConstant.MOVE_NUM);
                extract = String.format("10^(%d)*%s", moveNum, col);
                break;
            case ColumnConstant.ROUND:
                int retainNum = data.getIntValue(ColumnConstant.RETAIN_NUM);
                extract = String.format("round(%s,%d)", col, retainNum);
                break;
            case ColumnConstant.CEIL:
            case ColumnConstant.FLOOR:
                extract = retainDecimalPlaces(col, transformType);
                break;
            case ColumnConstant.STRING_REPEAT:
                repeatNum = data.getIntValue(ColumnConstant.REPEAT_NUM);
                extract = String.format("repeat(cast(%s as text), %d)", col, repeatNum);
                break;
            case ColumnConstant.WORD_REMOVE:
                extract = wordRemove(col);
                break;
            case ColumnConstant.WORD_RETAIN:
                extract = wordRetain(col);
                break;
            case ColumnConstant.STRING_INSERT:
                extract = stringInsert(col);
                break;
            case ColumnConstant.WORD_CASE:
                extract = wordCase(col);
                break;
            case ColumnConstant.WORD_INITIAL:
                extract = wordInitial(col);
                break;
            case ColumnConstant.SPECIAL_CHARACTER_REPLACE:
                String replaceValue = SqlHelper
                    .stringEscapeHelper(data.getString(ColumnConstant.REPLACE_VALUE));
                String originValue = SqlHelper
                    .stringEscapeHelper(data.getString(ColumnConstant.ORIGIN_VALUE));
                extract = String
                    .format("regexp_replace(%s::varchar, '%s', '%s', 'g')", col, originValue,
                        replaceValue);
                break;
            case ColumnConstant.CURSOR_REMOVE:
                int cursorStart = data.getIntValue(ColumnConstant.CURSOR_START);
                int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
                int length = cursorEnd - cursorStart;
                extract = String
                    .format("overlay(%s placing '' from %d for %d)", col, cursorStart + 1,
                        length);
                break;
            case ColumnConstant.CURSOR_RETAIN:
                cursorStart = data.getIntValue(ColumnConstant.CURSOR_START);
                cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
                length = cursorEnd - cursorStart;
                extract = String
                    .format("substring(%s from %d for %d)", col, cursorStart + 1, length);
                break;
            case ColumnConstant.DATE_FORMAT:
                extract = dateFormatExtract(col);
                break;
            case ColumnConstant.DEFAULT_EXTRACT:
                extract = defaultExtract(col);
                break;
            default:
                return "";
        }
        extract = extract + " as " + newCol;
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        int index = cols.indexOf(originalCol);
        cols = SqlUtil.formatPGSqlCols(cols);
        cols.add(index + 1, extract);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 其他条件未匹配上的处理逻辑
     */
    private String defaultExtract(String col) {
        JSONArray params = data.getJSONArray(ColumnConstant.PARAMS);
        List<String> list = Lists.newArrayList();
        for (int i = 0; i < params.size(); i++) {
            JSONObject json = params.getJSONObject(i);
            String source = SqlHelper.stringEscapeHelper(json.getString("source"));
            String target = SqlHelper.stringEscapeHelper(json.getString("target"));
            list.add(String.format("when %s = '%s' then '%s'", col, source, target));
        }
        return String.format("case %s else '%s' end", Joiner.on(" ").join(list),
            params.getJSONObject(params.size() - 1).getString("target"));
    }

    /**
     * 日期转成指定格式的字符串
     */
    private String dateFormatExtract(String col) {
        JSONArray params = data.getJSONArray(ColumnConstant.PARAMS);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < params.size(); i++) {
            JSONObject json = params.getJSONObject(i);
            String timeUnit = json.getString(ColumnConstant.TIME_UNIT);
            String desc = json.getString(ColumnConstant.DESC);
            desc = StringUtils.isEmpty(desc) ? "" : desc;
            sb.append(timeUnit).append(desc);
        }
        return String.format("to_char(%s, '%s')", col, sb.toString());
    }

    /**
     * 单词缩写
     */
    private String wordInitial(String col) {
        JSONArray json = data.getJSONArray(ColumnConstant.WORDS);
        int maxSplit = data.getIntValue("maxSplit");
        Map<Integer, String> caseTypeMap = Maps.newHashMap();
        if (CollectionUtil.isNotEmpty(json)) {
            maxSplit = Math.max(maxSplit,
                json.getJSONObject(json.size() - 1).getIntValue(ColumnConstant.WORD_INDEX));
            for (int i = 0; i < json.size(); i++) {
                JSONObject word = json.getJSONObject(i);
                caseTypeMap.put(word.getInteger(ColumnConstant.WORD_INDEX),
                    word.getString(ColumnConstant.CASE_TYPE));
            }
        }
        List<String> list = Lists.newArrayList();
        for (int i = 1; i <= maxSplit; i++) {
            String str = String.format("(left(split_part(%s, ' ', %d),1))", col, i);
            if (null != caseTypeMap.get(i)) {
                list.add(caseTypeMap.get(i) + str);
            } else {
                list.add(str);
            }
        }
        return String.format("concat(%s)", Joiner.on(",").join(list));
    }

    /**
     * 单词的大小写切换
     */
    private String wordCase(String col) {
        JSONArray wordCase = data.getJSONArray(ColumnConstant.WORD_CASE_PARAM);
        JSONObject firstParam = wordCase.getJSONObject(0);
        int firstIndex = firstParam.getIntValue(ColumnConstant.WORD_INDEX);
        int index = firstIndex - 1;

        String unit = String.format("(regexp_split_to_array(%s, ' ')::text[])[%s]", col, "%d");
        List<String> oldStrList = Lists.newArrayList();
        List<String> newStrList = Lists.newArrayList();
        for (int i = 0; i < wordCase.size(); i++) {
            JSONObject wordParam = wordCase.getJSONObject(i);
            int wordIndex = wordParam.getIntValue(ColumnConstant.WORD_INDEX);
            String caseType = firstParam.getString(ColumnConstant.CASE_TYPE);
            //不需要转换大小写的单词
            if (wordIndex > index + 1) {
                for (int j = index + 1; j < wordIndex; j++) {
                    oldStrList.add(String.format(unit, j));
                    newStrList.add(String.format(unit, j));
                }
            }
            oldStrList.add(String.format(unit, wordIndex));
            if (ColumnConstant.UPPER.equals(caseType) || ColumnConstant.LOWER.equals(caseType)) {
                newStrList.add(caseType + "(" + String.format(unit, wordIndex) + ")");
            } else if (ColumnConstant.FIRST_UPPER.equals(caseType)) {
                String temp = String.format(unit, wordIndex);
                newStrList.add(String
                    .format("\"overlay\"(%s, upper( substring( %s from 1 for 1 ) ), 1)", temp,
                        temp));
            } else if (ColumnConstant.FIRST_LOWER.equals(caseType)) {
                String temp = String.format(unit, wordIndex);
                newStrList.add(String
                    .format("\"overlay\"(%s, lower( substring( %s from 1 for 1 ) ), 1)", temp,
                        temp));
            }
            index = wordIndex;
        }
        String oldStr = Joiner.on(",").join(oldStrList);
        String newStr = Joiner.on(",").join(newStrList);
        return String
            .format("regexp_replace(%s::varchar, concat_ws(' ', %s), concat_ws(' ', %s))", col,
                oldStr, newStr);
    }

    /**
     * 在中间插入字符串
     */
    private String stringInsert(String col) {
        int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
        String insertValue = SqlHelper
            .stringEscapeHelper(data.getString(ColumnConstant.INSERT_VALUE));
        List<String> list = Lists.newArrayList();
        String template = String.format("(regexp_split_to_array(%s, ' ')::text[])[%s]", col, "%d");
        list.add(String.format(template, wordIndex - 1));
        list.add(String.format(template, wordIndex));
        list.add(String.format(template, wordIndex + 1));
        String oldStr = Joiner.on(",").join(list);
        list.add(wordIndex, "'" + insertValue + "'");
        String newStr = Joiner.on(",").join(list);
        return String
            .format("regexp_replace(%s::varchar,  concat_ws(' ', %s), concat_ws(' ',  %s))",
                col, oldStr, newStr);
    }

    /**
     * 保留n个单词
     */
    private String wordRetain(String col) {
        int starts = data.getIntValue(ColumnConstant.STARTS);
        int retainNum = data.getIntValue(ColumnConstant.RETAIN_NUM);

        String unit = "(regexp_split_to_array(%s, ' ')::text[])[%d]";
        String retainStr;
        List<String> list = Lists.newArrayList();
        if (retainNum == 0) {
            return "''";
        } else if (retainNum > 0) {
            for (int i = starts; i < starts + retainNum; i++) {
                list.add(String.format(unit, col, i));
            }
        } else {
            for (int i = starts + retainNum + 1; i <= starts; i++) {
                list.add(String.format(unit, col, i));
            }
        }
        retainStr = Joiner.on(",").join(list);

        return String.format("concat_ws(' ', %s)", retainStr);

    }

    /**
     * 移除n个单词
     */
    private String wordRemove(String col) {
        int starts = data.getIntValue(ColumnConstant.STARTS);
        int removeNum = data.getIntValue(ColumnConstant.REMOVE_NUM);
        int maxSplit = data.getIntValue("maxSplit");
        String unit = "(regexp_split_to_array(%s, ' ')::text[])[%d]";
        String removeStr;
        List<String> list = Lists.newArrayList();
        if (removeNum == 0) {
            return col;
        } else if (removeNum > 0) {
            for (int i = 1; i <= maxSplit; i++) {
                if (i >= starts && i < starts + removeNum) {
                    continue;
                }
                list.add(String.format(unit, col, i));
            }
        } else {
            for (int i = 1; i <= maxSplit; i++) {
                if (i > starts - removeNum && i <= starts) {
                    continue;
                }
                list.add(String.format(unit, col, i));
            }
        }
        removeStr = Joiner.on(",").join(list);
        return String.format("concat_ws(' ', %s)", removeStr);
    }


    /**
     * 向上/向下保留n位小数
     */
    private String retainDecimalPlaces(String col, String transformType) {
        int retainNum = data.getIntValue(ColumnConstant.RETAIN_NUM);
        //向上或向下保留
        String symbol = ColumnConstant.CEIL.equals(transformType) ? "+" : "-";
        return String
            .format("round(cast((%s %s 10^(-%d)*0.5) as numeric), %d)", col, symbol, retainNum,
                retainNum);
    }

    /**
     * 根据多项式导出新列
     */
    private String polynomialExtract(String col) {
        JSONArray coefficient = data.getJSONArray(ColumnConstant.COEFFICIENT);
        String template = "%s*%s^%d";
        List<String> list = Lists.newArrayList();
        for (int i = 0; i < coefficient.size(); i++) {
            list.add(String.format(template, coefficient.getString(i), col, i));
        }
        return "cast(cast(" + Joiner.on(" + ").join(list) + "as real) as numeric)";
    }

    /**
     * 删除/保留所选行
     */
    private String transformDeleteOrKeep(ActionEnum action) {
        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String col = data.getString(ColumnConstant.COL);
        col = SqlUtil.formatPGSqlColName(col);
        Set<String> cols = columnTypes.keySet();
        String where = "";
        if (ColumnConstant.SELECT_NULL.equals(transformType)) {
            List<String> colList = Lists.newArrayList();
            for (String colName : cols) {
                String sql;
                if (ActionEnum.TRANSFORM_DELETE == action) {
                    sql = "\"" + colName + "\" is not null";
                    if (SqlUtil.isString(columnTypes.get(colName))) {
                        sql = sql + " and \"" + colName + "\" != ''";
                    }
                } else {
                    sql = "\"" + colName + "\" is null";
                    if (SqlUtil.isString(columnTypes.get(colName))) {
                        sql = sql + " or \"" + colName + "\" = ''";
                    }
                }
                colList.add(sql);
            }
            where = "WHERE " + Joiner.on(ActionEnum.TRANSFORM_DELETE == action ? " and " : " or ")
                .join(colList);
        } else if (ColumnConstant.WORD_POSITION.equals(transformType)) {
            int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
            if (ActionEnum.TRANSFORM_DELETE == action) {
                where = String.format("WHERE split_part(%s, ' ', %d) = ''", col, wordIndex);
                where += " OR " + col + " IS NULL";
            } else {
                where = String.format("WHERE split_part(%s, ' ', %d) != ''", col, wordIndex);
            }
        } else if (ColumnConstant.AFTER_BEFORE.equals(transformType)) {
            String after = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.AFTER));
            String before = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BEFORE));
            String brushValue = getBrushValueSql(after, before, col);
            if (ActionEnum.TRANSFORM_DELETE == action) {
                where = "WHERE " + brushValue + " is null";
                where += " OR " + col + " IS NULL";
            } else {
                where = "WHERE " + brushValue + " is not null";
            }
        } else if (ColumnConstant.POSITION.equals(transformType)) {
            int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
            if (ActionEnum.TRANSFORM_DELETE == action) {
                where = String.format("WHERE char_length(%s) < %d", col, cursorEnd);
                where += " OR " + col + " IS NULL";
            } else {
                where = String.format("WHERE char_length(%s) >= %d", col, cursorEnd);
            }
        }
        String selectSql = buildSelectSql(SqlUtil.formatPGSqlCols(cols).iterator(), sourceTable);
        selectSql = selectSql + where;
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 拆分列
     */
    private String transformSplit() {
        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String originalCol = data.getString(ColumnConstant.COL);
        String col = SqlUtil.formatPGSqlColName(originalCol);
        JSONArray newCols = data.getJSONArray(ColumnConstant.NEW_COL);
        int splitNum = data.getIntValue(ColumnConstant.SPLIT_NUM);
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        cols = SqlUtil.formatPGSqlCols(cols);
        int maxNum = 1;
        boolean isEmpty = CollectionUtil.isEmpty(newCols);
        while (isEmpty) {
            String tempCol = originalCol + "_拆分" + maxNum;
            if (!columnTypes.containsKey(tempCol)) {
                maxNum--;
                break;
            }
            maxNum++;
        }
        List<String> tempCols = Lists.newArrayList();
        if (ColumnConstant.WORD_POSITION.equals(transformType)) {
            int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
            for (int i = 1; i <= splitNum; i++) {
                String newColName;
                if (isEmpty) {
                    newColName = originalCol + "_拆分" + (maxNum + i);
                } else {
                    newColName = newCols.getString(i - 1);
                }
                newColName = SqlUtil.formatPGSqlColName(newColName);
                tempCols.add(
                    String.format("btrim(split_part(%s, split_part(%s, ' ', %d), %d)) as %s",
                        col, col, wordIndex, i, newColName));
            }
        } else if (ColumnConstant.AFTER_BEFORE.equals(transformType)) {
            String after = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.AFTER));
            String before = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BEFORE));
            String brushValue = getBrushValueSql(after, before, col);
            for (int i = 1; i <= splitNum; i++) {
                String newColName;
                if (isEmpty) {
                    newColName = originalCol + "_拆分" + (maxNum + i);
                } else {
                    newColName = newCols.getString(i - 1);
                }
                newColName = SqlUtil.formatPGSqlColName(newColName);
                tempCols.add(String.format(
                    "btrim(split_part(%s, case when %s is null then '' else %s end, %d)) as %s",
                    col, brushValue, brushValue, i, newColName));
            }
        } else if (ColumnConstant.POSITION.equals(transformType)) {
            int cursorStart = data.getIntValue(ColumnConstant.CURSOR_START);
            int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
            int length = cursorEnd - cursorStart;
            for (int i = 1; i <= splitNum; i++) {
                String newColName;
                if (isEmpty) {
                    newColName = originalCol + "_拆分" + (maxNum + i);
                } else {
                    newColName = newCols.getString(i - 1);
                }
                newColName = SqlUtil.formatPGSqlColName(newColName);
                tempCols.add(String.format(
                    "btrim(split_part(%s, substring(%s from %d for %d), %d)) as %s",
                    col, col, cursorStart + 1, length, i, newColName));
            }
        }
        cols.addAll(0, tempCols);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 导出新列
     */
    private String transformExtract() {
        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String newCol = SqlUtil.formatPGSqlColName(data.getString(ColumnConstant.NEW_COL));
        String col = data.getString(ColumnConstant.COL);
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        int index = cols.indexOf(col);
        cols = SqlUtil.formatPGSqlCols(cols);
        col = SqlUtil.formatPGSqlColName(col);
        String extractSql = "";
        if (ColumnConstant.BRUSH_CONTENT.equals(transformType)) {
            String brushValue = SqlHelper.regularEscape(data.getString(ColumnConstant.BRUSH_VALUE));
            extractSql = String.format("substring(%s from '%s') as %s", col, brushValue, newCol);
        } else if (ColumnConstant.STARTS_WITH.equals(transformType)) {
            String startsValue = SqlHelper
                .regularEscape(data.getString(ColumnConstant.STARTS_VALUE));
            extractSql = String
                .format(
                    "case when %s = '%s' then %s else substring(%s from '#\"%s#\" %s' for '#') end as %s",
                    col, startsValue, col, col, startsValue, "%", newCol);
        } else if (ColumnConstant.ENDS_WITH.equals(transformType)) {
            String endsValue = SqlHelper.regularEscape(data.getString(ColumnConstant.ENDS_VALUE));
            extractSql = String
                .format(
                    "case when %s = '%s' then %s else substring(%s from '%s #\"%s#\"' for '#') end as %s",
                    col, endsValue, col, col, "%", endsValue, newCol);
        } else if (ColumnConstant.WORD_POSITION.equals(transformType)) {
            int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
            extractSql = String.format("split_part(%s, ' ', %d) as %s", col, wordIndex, newCol);
        } else if (ColumnConstant.AFTER_BEFORE.equals(transformType)) {
            String after = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.AFTER));
            String before = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BEFORE));
            extractSql = getBrushValueSql(after, before, col) + " as " + newCol;
        } else if (ColumnConstant.POSITION.equals(transformType)) {
            int cursorStart = data.getIntValue(ColumnConstant.CURSOR_START);
            int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
            int length = cursorEnd - cursorStart;
            extractSql = String
                .format("substring(%s from %d for %d) as %s", col, cursorStart + 1, length,
                    newCol);
        }
        cols.add(index + 1, extractSql);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 替换值
     */
    private String transformReplace() {
        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String replaceValue = SqlHelper
            .stringEscapeHelper(data.getString(ColumnConstant.REPLACE_VALUE));
        String col = data.getString(ColumnConstant.COL);
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        int index = cols.indexOf(col);
        cols.remove(index);
        cols = SqlUtil.formatPGSqlCols(cols);
        col = SqlUtil.formatPGSqlColName(col);
        String replaceSql = "";
        if (ColumnConstant.BRUSH_CONTENT.equals(transformType)) {
            String brushValue = SqlHelper.regularEscape(
                SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BRUSH_VALUE)));
            replaceSql = String
                .format(TRANSFORM_REPLACE_SQL, col, brushValue, replaceValue, ",'g'", col);
        }
        if (ColumnConstant.STARTS_WITH.equals(transformType)) {
            String startsValue = SqlHelper.regularEscape(
                SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.STARTS_VALUE)));
            replaceSql = String
                .format(TRANSFORM_REPLACE_SQL, col, "^" + startsValue, replaceValue, "", col);
        }
        if (ColumnConstant.ENDS_WITH.equals(transformType)) {
            String endsValue = SqlHelper.regularEscape(
                SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.ENDS_VALUE)));
            replaceSql = String
                .format(TRANSFORM_REPLACE_SQL, col, endsValue + "$", replaceValue, "", col);
        }
        if (ColumnConstant.POSITION.equals(transformType)) {
            int cursorStart = data.getIntValue(ColumnConstant.CURSOR_START);
            int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
            int length = cursorEnd - cursorStart;
            String replacePosition = String
                .format(REPLACE_POSITION_SQL, col, replaceValue, cursorStart + 1, length);
            String where = String.format("char_length(%s) >= %d", col, cursorEnd);
            replaceSql = String.format(TRANSFORM_SET_SQL, where, replacePosition, col, col);
        }
        if (ColumnConstant.WORD_POSITION.equals(transformType)) {
            int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
            replaceSql = String
                .format(WORD_POSITION_REPLACE, col, wordIndex, col, col, col, wordIndex,
                    replaceValue, col);
        }
        if (ColumnConstant.AFTER_BEFORE.equals(transformType)) {
            String after = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.AFTER));
            String before = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BEFORE));
            String brushValue = getBrushValueSql(after, before, col);
            if (StringUtils.isNotEmpty(after) && StringUtils.isNotEmpty(before)) {
                replaceSql = String
                    .format(
                        "case when %s is null then %s else regexp_replace(%s::varchar, concat('%s ' , %s, ' %s'), '%s %s %s') end as %s",
                        brushValue, col, col, after, brushValue, before, after,
                        replaceValue, before, col);
            } else if (StringUtils.isNotEmpty(after)) {
                replaceSql = String
                    .format(
                        "case when %s is null then %s else regexp_replace(%s::varchar, concat('%s ', %s), '%s %s') end as %s",
                        brushValue, col, col, after, brushValue, after, replaceValue, col);
            } else if (StringUtils.isNotEmpty(before)) {
                replaceSql = String
                    .format(
                        "case when %s is null then %s else regexp_replace(%s::varchar, concat(%s, ' %s'), '%s %s') end as %s",
                        brushValue, col, col, brushValue, before, replaceValue, before,
                        col);
            }
        }
        if (ColumnConstant.FILTER.equals(transformType)) {//替换当前单元格刷选内容
            String where = filter();
            where = where.replaceAll("WHERE", "");
            String brushValue = SqlHelper.regularEscape(
                SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BRUSH_VALUE)));
            String replace = String
                .format("btrim(regexp_replace(%s::varchar, E'%s', '%s' %s))", col, brushValue,
                    replaceValue, ",'g'");
            replaceSql = String
                .format("case when %s then %s else %s end as %s", where, replace, col, col);
        }
        if (ColumnConstant.MULTIPLE_CELL.equals(transformType)) {
            JSONArray params = data.getJSONArray(ColumnConstant.PARAMS);
            List<String> list = Lists.newArrayList();
            for (int i = 0; i < params.size(); i++) {
                JSONObject obj = params.getJSONObject(i);
                String recordId = obj.getString(IDCOLUMN_NAME);
                String brushValue = SqlHelper.regularEscape(
                    SqlHelper.stringEscapeHelper(obj.getString(ColumnConstant.BRUSH_VALUE)));
                String replace = String
                    .format("btrim(regexp_replace(%s::varchar, E'%s', '%s' %s))", col,
                        brushValue, replaceValue, ",'g'");
                list.add(String.format("when \"_record_id_\" = %s then %s", recordId, replace));
            }
            replaceSql = String
                .format("case %s else %s end as %s", Joiner.on(" ").join(list), col, col);
        }
        cols.add(index, replaceSql);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 获取在A/B字符串前/后的值的sql
     */
    public static String getBrushValueSql(String after, String before, String col) {
        String brushValue = "";
        if (StringUtils.isNotEmpty(after) && StringUtils.isNotEmpty(before)) {
            brushValue = String
                .format("substring(%s from '\\m%s ([^[:space:]]+) %s\\M')", col, after, before);
        } else if (StringUtils.isNotEmpty(after)) {
            brushValue = String.format("substring(%s from '\\m%s ([^[:space:]]+)')", col, after);
        } else if (StringUtils.isNotEmpty(before)) {
            brushValue = String.format("substring(%s from '([^[:space:]]+) %s\\M')", col, before);
        }
        return brushValue;
    }

    /**
     * 去除重复行
     */
    private String transformRemove() {
        JSONArray partitionCols = data.getJSONArray(ColumnConstant.PARTITION_COLS);
        String sortCol = data.getString(ColumnConstant.SORT_COL);
        String sortVal = data.getString(ColumnConstant.SORT_VAL);
        String orderBy = "";
        if (StringUtils.isNotEmpty(sortCol) && StringUtils.isNotEmpty(sortVal)) {
            orderBy = "order by " + SqlUtil.formatPGSqlColName(sortCol) + " " + sortVal;
        }
        List<String> partitionColsList = partitionCols.toJavaList(String.class);
        List<String> cols = SqlUtil.formatPGSqlCols(partitionColsList);
        String str = String
            .format(TRANSFORM_REMOVE_SQL, Joiner.on(",").join(cols), orderBy, sourceTable);
        String selectSql = buildSelectSql(SqlUtil.formatPGSqlCols(columnTypes.keySet()).iterator(),
            str);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 为单元格赋新值
     */
    private String transformSet() {
        //是否添加新列
        String newColumn = data.getString(ColumnConstant.NEW_COL);

        String transformType = data.getString(ColumnConstant.TRANSFORM_TYPE);
        String col = data.getString(ColumnConstant.COL);

        //值替换策略 (TODO NAP-2730 未实现 )
        ValueHandlingContext strategyContext = ValueHandlingContext.getInstance();
        strategyContext.chooseStrategy(data.getString(ColumnConstant.SET_VALUE_POLICY));
        String setValue = "";
        if (strategyContext.isManual()){
            setValue = data.getString(ColumnConstant.SET_VALUE);
        }else{
            setValue = strategyContext.executeStrategy(1, 2);
        }
        if (StringUtils.isEmpty(setValue) && (SqlUtil.isNumeric(columnTypes.get(col)) || SqlUtil
            .isDate(columnTypes.get(col)))) {
            setValue = "null";
        } else {
            setValue = "'" + SqlHelper.stringEscapeHelper(setValue) + "'";
        }

        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        int index = cols.indexOf(col);
        cols.remove(index);
        cols = SqlUtil.formatPGSqlCols(cols);
        col = SqlUtil.formatPGSqlColName(col);
        String where = "";
        if (ColumnConstant.FILTER.equals(transformType)) {//包含了行选择，空值筛选
            where = filter();
            where = where.replaceAll("WHERE", "");
        } else if (ColumnConstant.STARTS_WITH.equals(transformType)) {
            String startsValue = data.getString(ColumnConstant.STARTS_VALUE);
            startsValue = SqlHelper.regularEscape(SqlHelper.stringEscapeHelper(startsValue));
            where = col + " ~ E'^" + startsValue + "'";
        } else if (ColumnConstant.ENDS_WITH.equals(transformType)) {
            String endsValue = data.getString(ColumnConstant.ENDS_VALUE);
            endsValue = SqlHelper.regularEscape(SqlHelper.stringEscapeHelper(endsValue));
            where = col + " ~ E'" + endsValue + "$'";
        } else if (ColumnConstant.POSITION.equals(transformType)) {
            int cursorEnd = data.getIntValue(ColumnConstant.CURSOR_END);
            where = String.format("char_length(%s) >= %d", col, cursorEnd);
        } else if (ColumnConstant.WORD_POSITION.equals(transformType)) {
            int wordIndex = data.getIntValue(ColumnConstant.WORD_INDEX);
            where = String.format("(regexp_split_to_array(%s, ' ')::text[])[%d] is not null", col,
                wordIndex);
        } else if (ColumnConstant.AFTER_BEFORE.equals(transformType)) {
            String after = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.AFTER));
            String before = SqlHelper.stringEscapeHelper(data.getString(ColumnConstant.BEFORE));
            String brushValue = getBrushValueSql(after, before, col);
            where = brushValue + " is not null";
        }

        String transformSet;
        if (null != newColumn && !newColumn.isEmpty()){
            //2.4新需求 创建新的列
            transformSet = String.format(TRANSFORM_SET_SQL, where, setValue, col, SqlUtil.formatPGSqlColName(newColumn));
            cols.add(index, col);
        }else {
            transformSet = String.format(TRANSFORM_SET_SQL, where, setValue, col, col);
        }
        cols.add(index, transformSet);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 字段拆分
     *
     * @return sql
     */
    private String actionColumnSplit() {
        String col = data.getString("col");
        String separator = data.getString("separator").replaceAll("'", "''");
        Integer splitType = data.getInteger("splitType");
        JSONArray newCols = data.getJSONArray(ColumnConstant.NEW_COL);
        String selectSql = null;

        List<String> list = Lists.newArrayList();
        int splitNum = data.getInteger("splitNum");
        if (splitType == ColumnConstant.SPLIT_ALL) {
            splitNum = data.getInteger("maxSplit");
        }
        boolean isEmpty = CollectionUtil.isEmpty(newCols);
        int maxNum = 1;
        while (isEmpty) {
            String tempCol = col + "_拆分" + maxNum;
            if (!columnTypes.containsKey(tempCol)) {
                maxNum--;
                break;
            }
            maxNum++;
        }
        if (splitType == ColumnConstant.SPLIT_FIRST || splitType == ColumnConstant.SPLIT_ALL) {
            for (int i = 1; i <= splitNum; i++) {
                String newColName;
                if (isEmpty) {
                    newColName = col + "_拆分" + (maxNum + i);
                } else {
                    newColName = newCols.getString(i - 1);
                }
                newColName = SqlUtil.formatPGSqlColName(newColName);
                String splitSql = String.format(SPLIT_FIRST_SQL, col, separator, i, newColName);
                list.add(splitSql);
            }
            selectSql = String.format(SPLIT_FIRST_FULL_SQL, Joiner.on(",").join(list), sourceTable);
        }
        if (splitType == ColumnConstant.SPLIT_LAST) {
            for (int i = 1; i <= splitNum; i++) {
                String newColName;
                if (isEmpty) {
                    newColName = col + "_拆分" + (maxNum + i);
                } else {
                    newColName = newCols.getString(i - 1);
                }
                newColName = SqlUtil.formatPGSqlColName(newColName);
                String splitSql = String
                    .format(SPLIT_LAST_SQL, col, separator, col, separator, i, newColName);
                list.add(splitSql);
            }
            selectSql = String
                .format(SPLIT_FIRST_FULL_SQL, Joiner.on(",").join(list), sourceTable);
        }
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 字段合并
     *
     * @return sql
     */
    private String actionColumnMerge() {
        String separator = data.getString("separator");
        separator = StringUtils.isEmpty(separator) ? "," : ",'" + separator + "',";
        JSONArray mergeCols = data.getJSONArray("mergeCols");
        List<String> strings = SqlUtil.formatPGSqlCols(mergeCols.toJavaList(String.class));
        String newCol = data.getString(ColumnConstant.NEW_COL);
        newCol = StringUtils.isEmpty(newCol) ? mergeCols.getString(0) : newCol;
        newCol = newCol.replaceAll("\"", "\"\"");
        String concatSql = String
            .format(MERGE_SQL, Joiner.on(separator).join(strings), newCol);
        List<String> colsSet = Lists.newLinkedList(columnTypes.keySet());
        colsSet.removeAll(mergeCols);
        List<String> cols = SqlUtil.formatPGSqlCols(colsSet);
        int index = colsSet.indexOf(mergeCols.get(mergeCols.size() - 1));
        cols.add(index + 1, concatSql);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 字段分组
     *
     * @return sql
     */
    private String actionGroup() {
        String col = data.getString("col");
        JSONArray groupConfig = data.getJSONArray("groupConfig");
        JSONObject config = groupConfig.getJSONObject(0);
        JSONArray sourceArr = config.getJSONArray("source");
        String target = config.getString("target");
        //只保存新值
        if (null != target && target.equals(sourceArr.getString(0))) {
            targetTable = sourceTable;
            return "";
        }
        String fields;
        String where = "";
        List<String> list = Lists.newArrayList();
        if (CollectionUtil.isNotEmpty(sourceArr)) {
            for (int i = 0; i < sourceArr.size(); i++) {
                if (null == sourceArr.getString(i)) {
                    where = String.format("\"%s\" IS NULL", col);
                } else {
                    list.add("'" + sourceArr.getString(i).replaceAll("'", "''") + "'");
                }
            }
            fields = Joiner.on(",").join(list);
            List<String> appendSql = Lists.newArrayList();
            if (StringUtils.isNotEmpty(where)) {
                appendSql.add(where);
            }
            if (StringUtils.isNotEmpty(fields)) {
                appendSql.add(String.format("\"%s\" IN (%s)", col, fields));
            }
            where = Joiner.on(" OR ").join(appendSql);
        }
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        int index = cols.indexOf(col);
        String targetVal = null == target ? "null" : "'" + target.replaceAll("'", "''") + "'";
        String groupSql = String.format(GROUP_SQL, where, targetVal, col);
        cols.remove(index);
        cols = SqlUtil.formatPGSqlCols(cols);
        cols.add(index, groupSql);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 修改字段类型
     *
     * @return sql
     */
    private String actionTransform() {
        JSONArray col = data.getJSONArray("col");
        if (CollectionUtil.isEmpty(col)) {
            return "";
        }
        String toType = data.getString("toType");
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        cols = SqlUtil.formatPGSqlCols(cols);
        List<String> columnNames = new ArrayList<>();
        for (int i = 0; i < col.size(); i++) {
            String colName = SqlUtil.formatPGSqlColName(col.getString(i));
            int index = cols.indexOf(colName);
            String newCol = "";
            if (toType.equals("date") && data.getJSONArray("semantic") != null && data.getJSONArray("semantic").getString(i).equals("id")){
                newCol = String.format("%s_出生日期", col.getString(i));
                newCol = ToolUtil.checkDuplicateName(newCol, cols);
                String formatCol = String.format("pipeline.sys_func_format_time(substring(cast(%s as varchar), 7, 8), '%s') as %s", colName, PythonDateTypeFormatEnum.FORMAT_3.getVal(), newCol);
                cols.add(index + 1, formatCol);
            } else {
                cols.remove(index);
                cols.add(index,
                    String.format(TRANSFORM_SQL, colName, DataTypeEnum.get(toType).getGpType()));
            }
            columnNames.add(newCol);
        }
        data.put("columnName", columnNames);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 转换格式
     *
     * @return sql
     */
    private String formatTransform() {
        String col = data.getString("col");
        String key = SqlUtil.formatPGSqlColName(col);
        String type = data.getString("type");
        String semantic = data.getString("semantic");
        String table = data.getString("table");
        String columnName = data.getString("columnName");
        columnName = SqlUtil.formatPGSqlColName(columnName);
        String format = data.getString("format");
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        List<String> formatCols = new ArrayList<>();
        int index = cols.indexOf(col);
        String formatCol = "";
        String selectSql = "";

        if (type.equals(DatasetConstant.DATA_DATE)){
            for (PythonDateTypeFormatEnum item: PythonDateTypeFormatEnum.values()){
                if (format.equals(item.getDesc())){
                    formatCols = SqlUtil.formatPGSqlCols(cols);
                    formatCol = String.format("pipeline.sys_func_format_time(\"%s\"::varchar, '%s') as %s", col, item.getVal(), columnName);
                    formatCols.add(index + 1, formatCol);
                    selectSql = buildSelectSql(formatCols.iterator(), sourceTable);
                }
            }
        } else {
            for (String colName : cols) {
                formatCols.add(String.format("b.%s as %s", colName, colName));
            }
            formatCol = String.format("a.%s as %s", format, columnName);
            formatCols.add(index + 1, formatCol);

            List<String> sqlItems = new ArrayList<>();
            switch (semantic) {
                case "country":
                    for (String item: SemanticConstant.COUNTRY_COL){
                        sqlItems.add(String.format("a.%s = b.%s", item, key));
                    }
                    selectSql = String.format("select %s from dataset._country_mapper_ a join %s b on %s;",
                        Joiner.on(",").join(formatCols), table, Joiner.on(" or ").join(sqlItems));
                    break;
                case "province":
                    for (String item: SemanticConstant.PROVINCE_COL){
                        sqlItems.add(String.format("a.%s = b.%s", item, key));
                    }
                    selectSql = String.format("select %s from dataset._province_mapper_ a join %s b on %s;",
                        Joiner.on(",").join(formatCols), table, Joiner.on(" or ").join(sqlItems));
                    break;
                case "city":
                    for (String item: SemanticConstant.CITY_COL){
                        sqlItems.add(String.format("a.%s = b.%s", item, key));
                    }
                    selectSql = String.format("select %s from dataset._city_mapper_ a join %s b on %s;",
                        Joiner.on(",").join(formatCols), table, Joiner.on(" or ").join(sqlItems));
                    break;
            }
        }
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 编辑有序类别顺序
     *
     * @return sql
     */
    private String editOrder() {
        String selectSql = String.format(SELECT_SQL, "*", sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * json按key提取新列
     *
     * @return sql
     */
    private String jsonParse() {
        String col = data.getString("col");
        JSONArray keys = data.getJSONArray("key");
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        List<String> formatCols = SqlUtil.formatPGSqlCols(cols);
        List<String> newCols = new ArrayList<>();
        int index = cols.indexOf(col);
        for (int i = 0; i < keys.size(); i++) {
            String key = keys.getString(i);
            String newColName = "";
            if (data.getJSONArray("newCol") != null) {
                newColName = data.getJSONArray("newCol").getString(i);
            } else {
                newColName = checkDuplicateName(String.format("%s_%s", col, key), cols);
            }
            newCols.add(newColName);
            String parseStr = String
                .format("\"%s\"::json ->> '%s' as \"%s\"", col, key, newColName);
            formatCols.add(index + 1, parseStr);
            index += 1;
        }
        data.put("newCols", newCols);
        String selectSql = buildSelectSql(formatCols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * array按key提取新列
     *
     * @return sql
     */
    private String arrayParse() {
        String col = data.getString("col");
        JSONArray indces = data.getJSONArray("index");
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        List<String> formatCols = SqlUtil.formatPGSqlCols(cols);
        List<String> newCols = new ArrayList<>();
        int index = cols.indexOf(col);
        for (int i = 0; i < indces.size(); i++) {
            int id = indces.getInteger(i);
            String newColName = "";
            if (data.getJSONArray("newCol") != null) {
                newColName = data.getJSONArray("newCol").getString(i);
            } else {
                newColName = checkDuplicateName(String.format("%s_%s", col, id + 1), cols);
            }
            newCols.add(newColName);
            String parseStr = String.format("\"%s\"::json ->> %s as \"%s\"", col, id, newColName);
            formatCols.add(index + 1, parseStr);
            index += 1;
        }
        data.put("newCols", newCols);
        String selectSql = buildSelectSql(formatCols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 多列合并为json
     *
     * @return sql
     */
    private String jsonMerge() {
        JSONArray mergeCols = data.getJSONArray("cols");
        String columnName = data.getString("columnName");
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        List<String> formatCols = SqlUtil.formatPGSqlCols(cols);
        int index = cols.indexOf(mergeCols.get(mergeCols.size() - 1));
        JSONArray concatStrs = new JSONArray();
        for (Object col : mergeCols) {
            concatStrs.add(String.format("'%s',\"%s\"", col, col));
        }
        String mergeStr = String.format(
            "replace(replace(replace(replace(replace(cast(json_build_object(%s) as varchar), " +
                "'\\\"', '\"'), '\"{', '{'), '}\"', '}'), '\"[', '['), ']\"', ']') as \"%s\"",
            Joiner.on(",").join(concatStrs.iterator()), columnName);
        formatCols.add(index + 1, mergeStr);
        String selectSql = buildSelectSql(formatCols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 多列合并为array
     *
     * @return sql
     */
    private String arrayMerge() {
        JSONArray mergeCols = data.getJSONArray("cols");
        String columnName = data.getString("columnName");
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        List<String> formatCols = SqlUtil.formatPGSqlCols(cols);
        int index = cols.indexOf(mergeCols.get(mergeCols.size() - 1));
        JSONArray concatStrs = new JSONArray();
        for (Object col : mergeCols) {
            concatStrs.add(String.format("\"%s\"", col));
        }
        String mergeStr = String.format(
            "replace(replace(replace(replace(replace(cast(json_build_array(%s) as varchar), " +
                "'\\\"', '\"'), '\"{', '{'), '}\"', '}'), '\"[', '['), ']\"', ']') as \"%s\"",
            Joiner.on(",").join(concatStrs.iterator()), columnName);
        formatCols.add(index + 1, mergeStr);
        String selectSql = buildSelectSql(formatCols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 仅保留字段
     *
     * @return sql
     */
    private String actionColumnRetain() {
        JSONArray retainCols = data.getJSONArray("retainCols");
        List<String> cols = retainCols.toJavaList(String.class);
        cols.add(IDCOLUMN_NAME);
        cols = SqlUtil.formatPGSqlCols(cols);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 移除字段
     *
     * @return sql
     */
    private String actionColumnRemove() {
        JSONArray removeCols = data.getJSONArray("removeCols");
        Set<String> colsSet = columnTypes.keySet();
        colsSet.removeAll(removeCols);
        List<String> cols = SqlUtil.formatPGSqlCols(colsSet);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 复制字段
     *
     * @return sql
     */
    private String actionColumnCopy() {
        String col = data.getString("col");
        Set<String> colsSet = columnTypes.keySet();
        List<String> cols = Lists.newLinkedList(colsSet);
        int index = cols.indexOf(col);
        cols = SqlUtil.formatPGSqlCols(cols);
        String newName = data.getString("columnName");
        cols.add(index + 1, String.format(RENAME_SQL, col, newName));
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 分组聚合
     */
    private String actionGroupBy() {
        String currentCol = data.getString("col");
        JSONArray groupByCols = data.getJSONArray("groupByCol");
        String groupByType = data.getString("aggrfun");
        int resultType = data.getInteger("resultType");
        Set<String> colsSet = columnTypes.keySet();
        List<String> cols = SqlUtil.formatPGSqlCols(Lists.newLinkedList(colsSet));

        String newName = groupByType + "_" + currentCol;
        if (colsSet.contains(newName)) {
            for (int i = 1; ; i++) {
                newName = groupByType + "_" + currentCol + "_" + i;
                if (!colsSet.contains(newName)) {
                    break;
                }
            }
        }

        String aggrString;
        if (groupByType.equals("avg")) {
            aggrString = "round(avg(\"" + currentCol + "\"), 2)";
        } else {
            aggrString = groupByType + "(\"" + currentCol + "\")";
        }

        String selectSql;
        if (resultType == 1) {
            String completeNewName;
            if (groupByCols == null || groupByCols.size() == 0) {
                completeNewName = String.format(GROUP_BY_EMPTY_TYPE1_INNER, aggrString, sourceTable,
                    "\"" + newName + "\"");
            } else {
                String whereClause = genGroupByClause(new StringBuilder(), groupByCols);
                completeNewName = String
                    .format(GROUP_BY_TYPE1_INNER, aggrString, sourceTable, whereClause,
                        "\"" + newName + "\"");
            }
            //插入在原始字段的附近，保证他们在相邻的位置
            cols.add(cols.indexOf("\"" + currentCol + "\"") + 1, completeNewName);
            selectSql = buildSelectSql(cols.iterator(), sourceTable) + " a";
            logger.info("SQL generated from actionGroupBy1 : {}", selectSql);
            return String.format(SqlTemplate.CREATE_TABLE_SQL, targetTable, selectSql);
        } else {
            if (groupByCols == null || groupByCols.size() == 0) {
                selectSql = String
                    .format(GROUP_BY_EMPTY_TYPE2, aggrString, groupByType, sourceTable);
            } else {
                StringBuilder groupCols = new StringBuilder();
                for (int i = 0; i < groupByCols.size(); i++) {
                    if (i != 0) {
                        groupCols.append(", ");
                    }
                    groupCols.append("\"").append(groupByCols.get(i)).append("\"");
                }
                selectSql = String
                    .format(GROUP_BY_TYPE2, groupCols, aggrString, groupByType, sourceTable,
                        groupCols);
            }
            logger.info("SQL generated from actionGroupBy2 : {}", selectSql);
            return String.format(SqlTemplate.CREATE_TABLE_SQL, targetTable, selectSql);
        }
    }

    /**
     * @param builder
     * @param colsArray
     * @return
     */
    private String genGroupByClause(StringBuilder builder, JSONArray colsArray) {
        for (int i = 0; i < colsArray.size(); i++) {
            String groupCol = colsArray.getString(i);
            if (i != 0) {
                builder.append(" and ");
            }
            //添加COALESCE, 添加如果为空的处理
            builder.append("COALESCE(");
            builder.append("a.\"").append(groupCol).append("\", ");

            appendDefaultValue(builder, columnTypes.get(groupCol));

            builder.append(")").append(" = ").append("COALESCE(");
            builder.append("b.\"").append(groupCol).append("\", ");

            appendDefaultValue(builder, columnTypes.get(groupCol));

            builder.append(")");
        }
        return builder.toString();
    }

    private void appendDefaultValue(StringBuilder builder, Integer dataType) {
        if (SqlUtil.isString(dataType)) {
            builder.append("\'null\'");
        } else if (SqlUtil.isInteger(dataType)) {
            builder.append(0);
        } else if (SqlUtil.isDecimal(dataType)) {
            builder.append(0.0);
        } else if (SqlUtil.isDate(dataType)) {
            builder.append("date(\'1970-01-01\')");
        }
    }

    /**
     * 字段重命名
     *
     * @return sql
     */
    private String actionRename() {
        String col = data.getString("col");
        String newName = data.getString("newCol");
        if (newName.trim().matches(DatasetConstant.SPECIAL_CHARACTER_REGEX)) {
            newName = newName.trim().replaceAll(DatasetConstant.SPECIAL_CHARACTER_REPLACE, "");
        }
        if (columnTypes.containsKey(newName)){
            return StringUtils.EMPTY;
        }
        return getString(col, String.format(RENAME_SQL, col, newName));
    }

    /**
     * 字段清理
     *
     * @param function 函数sql
     * @return sql
     */
    private String actionCleanUp(String function) {
        String col = data.getString("col");
        return getString(col, String.format(function, col, col));
    }

    private String getString(String col, String format) {
        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
        if (!cols.contains(col)){
            return String.format("error: column %s does not exist", col);
        }
        int index = cols.indexOf(col);
        cols.remove(index);
        cols = SqlUtil.formatPGSqlCols(cols);
        cols.add(index, format);
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 筛选器的sql
     */
    private String actionFilter() {
        String where = filter();
        List<String> cols = SqlUtil.formatPGSqlCols(columnTypes.keySet());
        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql + where);
    }

    public String filter() {
        JSONArray filter = data.getJSONArray("filter");
        resolveNull(filter);
        String col = filter.getJSONArray(0).getJSONObject(0).getString("col");
        if (!columnTypes.containsKey(col)) {
            data.put("table", targetTable);
            throw new RuntimeException("column " + col + " dose not exist in " + sourceTable);
        }
        List<ConfigComponent> configComponents = parseFilter(data);
        Map<String, Integer> bindMap = Maps.newHashMap();
        Set<Entry<String, Integer>> entries = columnTypes.entrySet();
        entries.forEach(entry -> bindMap.put(entry.getKey().toUpperCase(), entry.getValue()));
        SqlHelper sqlHelper = new SqlHelper();
        sqlHelper.bind(bindMap);
        String where = sqlHelper.assembleFilter(configComponents);
        System.out.println("where -> 1"  + where);
        //整数的模糊匹配
        if (configComponents.size() == 1 && configComponents.get(0) instanceof DimensionConfig) {
            DimensionConfig config = (DimensionConfig) configComponents.get(0);
            String fieldName = config.getFieldName();
            String filterType = config.getFilterType();
            Integer type = columnTypes.get(fieldName);
            if (isIntFuzzyMatching(filterType, type)) {
                where = where.replaceAll(col, String.format("CAST(\"%s\" as text)", col));
            }
        }
        //字符串类型，空值包含空字符串
        if (configComponents.size() == 1 && SqlUtil.isString(columnTypes.get(col)) && where
            .toUpperCase().contains("NULL")) {
            if (where.toUpperCase().contains("IS NULL")) {
                where = where + " OR \"" + col + "\" = ''";
            } else if (where.toUpperCase().contains("IS NOT NULL")) {
                where = where.replaceAll("IS NOT NULL", " != '' ");
            }
        }
        where = formatPGSqlColName(filter, where);
        JSONObject tmpFilter = filter.getJSONArray(0).getJSONObject(0);
        if (!tmpFilter.getString("filterType").equals("=") && columnTypes.get(col) == Types.DATE){
            String formatCol = String.format("pipeline.sys_func_format_time(\"%s\"::varchar, '%s')", col, PythonDateTypeFormatEnum.FORMAT_0.getVal());
            where = where.replaceAll(SqlUtil.formatPGSqlColName(col), formatCol);
        }
        return where;
    }

    private void resolveNull(JSONArray filter) {
        if (filter.size() != 1) {
            return;
        }
        JSONArray arr = filter.getJSONArray(0);
        if (arr.size() != 1) {
            return;
        }
        JSONObject object = arr.getJSONObject(0);
        JSONArray oldValues = object.getJSONArray("values");
        if (oldValues.contains("#NULL")) {
            return;
        }
        String type = object.getString("filterType");
        if ("!~".equals(type) || "<>".equals(type) || "not starts with".equals(type)
            || "not ends with".equals(type)) {
            JSONObject obj = new JSONObject();
            obj.put("col", object.getString("col"));
            obj.put("filterType", "=");
            JSONArray values = new JSONArray();
            values.add("#NULL");
            obj.put("values", values);
            arr.add(obj);
        }
    }

    /**
     * 构建select语句
     */
    public static String buildSelectSql(Iterator<?> columns, String table) {
        String cols = Joiner.on(",").join(columns);
        return String.format(SELECT_SQL, cols, table);
    }

    /**
     * 构建with语句
     */
    public static String buildWithSql(String withName, String sql1, String sql2) {
        return String.format(WITH_SQL, withName, sql1, sql2);
    }

    /**
     * 解析筛选条件
     */
    public static List<ConfigComponent> parseFilter(JSONObject data) {
        return parseFilter(data, "filter");
    }

    public static List<ConfigComponent> parseFilter(JSONObject data, String key) {
        List<ConfigComponent> filters = Lists.newArrayList();

        JSONArray wdFilterArray = data.getJSONArray(key);
        if (wdFilterArray != null && !wdFilterArray.isEmpty()) {
            for (int i = 0; i < wdFilterArray.size(); i++) {
                JSONArray subFilterArray = wdFilterArray.getJSONArray(i);
                ConfigComponent component = null;
                if (subFilterArray.size() > 1) {
                    CompositeConfig composite = new CompositeConfig();
                    composite.setType("OR");
                    for (int j = 0; j < subFilterArray.size(); j++) {
                        JSONObject jsonObj = subFilterArray.getJSONObject(j);
                        DimensionConfig dc = resolveSingle(jsonObj);
                        composite.add(dc);
                    }
                    component = composite;
                } else {
                    if (subFilterArray.size() > 0) {
                        JSONObject jsonObj = subFilterArray.getJSONObject(0);
                        component = resolveSingle(jsonObj);
                    }
                }
                if (component != null) {
                    filters.add(component);
                }
            }
        }

        return filters;
    }

    public static DimensionConfig resolveSingle(JSONObject jsonObj) {
        DimensionConfig filterDC = new DimensionConfig();
        filterDC.setFieldName(jsonObj.getString("col"));
        filterDC.setFilterType(jsonObj.getString("filterType"));
        filterDC.setValues(jsonObj.getJSONArray("values").toJavaList(String.class));
        if (jsonObj.containsKey("includeNull")) {
            filterDC.setIncludeNull(jsonObj.getBooleanValue("includeNull"));
        }

        JSONArray checkedList = jsonObj.getJSONArray("checkedList");
        if (CollectionUtil.isEmpty(checkedList)) {
            return filterDC;
        }
        List<String> checkedVals = checkedList.toJavaList(String.class);
        List<String> formatVals = Lists.newArrayList();

        if ("datetime".equals(jsonObj.getString("type"))) {
            String format = jsonObj.getString("format");
            for (String checkedVal : checkedVals) {
                try {
                    String formatVal = TimeUtil
                        .format(checkedVal, format, TimeUtil.FULL_DATE_FORMAT);
                    formatVals.add(formatVal);
                } catch (ParseException e) {
                    logger.error(
                        "filter do format-checked error(" + checkedVal + ", " + format + ")",
                        e);
                }
            }
        } else if ("[a,b]".equals(jsonObj.getString("filterType")) && "timestamp"
            .equals(jsonObj.getString("type"))) {
            String format = jsonObj.getString("format");

            if (CollectionUtils.isNotEmpty(checkedVals) && checkedVals.size() == 2) {
                try {
                    String formatStartTime = TimeUtil
                        .format(checkedVals.get(0), format, TimeUtil.FULL_DATE_FORMAT);
                    String formatEndTime = TimeUtil.completeTimestamp(checkedVals.get(1), format);

                    formatVals.add(formatStartTime);
                    formatVals.add(formatEndTime);
                } catch (ParseException e) {
                    logger.error(
                        "filter do format-checked error(" + checkedVals.get(0) + ", " + format
                            + ")",
                        e);
                }
            }

        } else {
            formatVals = checkedVals;
        }
        filterDC.setValues(formatVals);
        return filterDC;
    }

    /**
     * 构建查询字段最大能拆分成几段的sql
     */
    public static String initQueryMaxSplitSql(JSONObject data) {
        String col = data.getString("col");
        String sourceTable = ToolUtil.alignTableName(data.getString("table"), 0L);
        String separator = data.getString("separator");
        if (separator.equals("*")) {
            separator = "\\*";
        }
        separator = separator.replaceAll("'", "''");
        return String.format(MAX_SPLIT_SQL, col, separator, col, sourceTable);
    }

    public static String initQueryWordNumberSql(JSONObject data) {
        String col = data.getString("col");
        String sourceTable = ToolUtil.alignTableName(data.getString("table"), 0L);
        return String.format(
            "select max(char_length(\"%s\") - char_length(replace(\"%s\", ' ', '')))+1 from (select \"%s\" from %s where \"_record_id_\" <= 100000) a",
            col, col, col, sourceTable);
    }

    /**
     * 将列名中的"转义，并在两头加"防止和保留字段冲突
     */
    public static String formatPGSqlColName(JSONArray filter, String sql) {
        for (int i = 0; i < filter.size(); i++) {
            JSONArray array = filter.getJSONArray(i);
            for (int j = 0; j < array.size(); j++) {
                JSONObject jsonObject = array.getJSONObject(j);
                String col = jsonObject.getString("col");
                sql = sql.replaceAll(" " + col + " ",
                    " \"" + col.replaceAll("\"", "\"\"") + "\" ");
            }
        }
        return sql;
    }

    /**
     * 判断是否是数值的模糊匹配
     */
    public static boolean isIntFuzzyMatching(String filterType, Integer type) {
        if (!SqlSyntaxHelper.isNumber(type)) {
            return false;
        }
        return "~".equals(filterType) || "!~".equals(filterType) || "starts with".equals(filterType)
            || "not starts with".equals(filterType) || "ends with".equals(filterType)
            || "not ends with"
            .equals(filterType);
    }

    /**
     * 根据公式新增列
     */
    private String addColumnOnFormula(boolean edit) {
        String selectSql = getAddColumnOnFormulaSelectSql(edit);
        logger.info("select sql : " + selectSql);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    public static String getWidgetFormulaSelectSql(Map<String, Integer> columnTypes, String tableName, String rawString) {
        List<String> cols = new ArrayList<>(columnTypes.keySet());
        Integer recordIdIndex = -1;
        try {
            recordIdIndex = cols.indexOf(IdConstant.RECORD_ID);
        }catch (Exception e){
            logger.warn("there id no _record_id_ in table {}, when execute formula.", tableName);
        }
        cols.removeIf(e -> e.equals(IdConstant.RECORD_ID));
        String cleanString = cleanInvalidString(rawString, widgetFunctionSet);
        String stringWithRealColumn = workWithFormula(cleanString, tableName);
        Matcher matcher = Pattern.compile("\\$c\\d+").matcher(stringWithRealColumn);
        int matcherStart = 0;
        while (matcher.find(matcherStart)) {
            String indexString = matcher.group(0);
            int columnIndex = Integer.parseInt(indexString.substring(2)) - 1;
            if (columnIndex >= cols.size()) {
                throw new FormulaException("列下标越界！");
            }
            // 列下标替换
            stringWithRealColumn = stringWithRealColumn.replaceFirst("\\" + indexString,
                    "\"" + cols.get(columnIndex) + "\"");
            matcherStart = matcher.end();
        }
        return String.format(SELECT_SQL, stringWithRealColumn, tableName);
    }

    private String getAddColumnOnFormulaSelectSql(boolean edit) {
        String rawString = data.getString("formula");
        String newColumn = data.getString("newColumn");
        String currentColumn = data.getString("col");
        Set<String> colsSet = columnTypes.keySet();
        colsSet.remove("_record_id_");
        List<String> cols = new ArrayList<>(colsSet);
        String cleanString = cleanInvalidString(rawString);
        String stringWithRealValue = workWithFormula(cleanString, sourceTable);
        String stringWithRealColumn = stringWithRealValue;
        Matcher matcher = Pattern.compile("\\$c\\d+").matcher(stringWithRealValue);
        // 新增列的下标
        int index = cols.indexOf(currentColumn) + 1;
        int matcherStart = 0;
        while (matcher.find(matcherStart)) {
            String indexString = matcher.group(0);
            // 表达式中的列下标
            int columnIndex = Integer.parseInt(indexString.substring(2)) - 1;
            if (!edit && columnIndex >= cols.size() || edit && columnIndex >= cols.size() - 1) {
                throw new FormulaException("列下标越界！");
            }
            int realIndex = edit && columnIndex >= index ? columnIndex + 1 : columnIndex;
            // 列下标替换
            stringWithRealColumn = stringWithRealColumn.replaceFirst("\\" + indexString,
                "\"" + cols.get(realIndex) + "\"");
            matcherStart = matcher.end();
        }
        String completeNewName = "(" + stringWithRealColumn + ") as \"" + newColumn + "\"";
        cols = SqlUtil.formatPGSqlCols(cols);
        if (edit) {
            cols.set(index, completeNewName);
        } else {
            cols.add(index, completeNewName);
        }
        for (int i = 0; i < cols.size(); i++) {
            if (i != index && cols.get(i).equals("\"" + newColumn + "\"")) {
                throw new FormulaException("列名已存在！");
            }
        }
        cols.add("\"_record_id_\"");
        return buildSelectSql(cols.iterator(), sourceTable);
    }

    public static String getWidgetFormulaSelectSql2(List<String> cols, String tableName, String rawString) {
        Integer recordIdIndex = -1;
        try {
            recordIdIndex = cols.indexOf(IdConstant.RECORD_ID);
        }catch (Exception e){
            System.out.println("there id no _record_id");
        }
        cols.removeIf(e -> e.equals(IdConstant.RECORD_ID));
        String cleanString = cleanInvalidString(rawString, widgetFunctionSet);
        String stringWithRealColumn = workWithFormula(cleanString, tableName);
        Matcher matcher = Pattern.compile("\\$c\\d+").matcher(stringWithRealColumn);
        int matcherStart = 0;
        while (matcher.find(matcherStart)) {
            String indexString = matcher.group(0);
            int columnIndex = Integer.parseInt(indexString.substring(2)) - 1;
            if (columnIndex >= cols.size()) {
                throw new FormulaException("列下标越界！");
            }
            // 列下标替换
            stringWithRealColumn = stringWithRealColumn.replaceFirst("\\" + indexString,
                    "\"" + cols.get(columnIndex) + "\"");
            matcherStart = matcher.end();
        }
        return String.format(SELECT_SQL, stringWithRealColumn, tableName);
    }

    /**
     * 替换特殊字符
     */
    public static String replaceSpecialValue(String stringWithoutFormula) {
        int[] quotations = getQuotationNum(stringWithoutFormula);
        StringBuilder result = new StringBuilder();
        String rule = "Math\\.E|Math\\.PI|true|false";
        Matcher matcher = Pattern.compile(rule).matcher(stringWithoutFormula);
        int index = 0;
        while (index < stringWithoutFormula.length()) {
            int svIndex;
            if (matcher.find(index)) {
                svIndex = matcher.start();
                result.append(stringWithoutFormula, index, svIndex);
                if (quotations[svIndex] % 2 == 1) {
                    result.append(matcher.group(0));
                } else {
                    switch (matcher.group(0)) {
                        case "Math.E":
                            result.append(Math.E);
                            break;
                        case "Math.PI":
                            result.append(Math.PI);
                            break;
                        case "true":
                            result.append(1);
                            break;
                        case "false":
                            result.append(0);
                            break;
                        default:
                            throw new RuntimeException("Special string not found!");
                    }
                }
                index = matcher.end();
            } else {
                break;
            }
        }
        result.append(stringWithoutFormula.substring(index));

        return result.toString();
    }

    /**
     * 获取每个字符前的引号数目
     */
    public static int[] getQuotationNum(String s) {
        int[] quotations = new int[s.length()];
        int cnt = 0;
        for (int i = 0; i < s.length(); i++) {
            if (s.charAt(i) == '\'') {
                cnt++;
            }
            quotations[i] = cnt;
        }

        return quotations;
    }

    /**
     * 处理公式
     */
    public static String workWithFormula(String cleanString, String sourceTable) {
        // 获取每个字符前的引号数，用来判定匹配到的关键词是否需要处理
        int[] quotations = getQuotationNum(cleanString);
        // 拼接正则表达式，用于匹配udf
        StringBuilder sb = new StringBuilder();
        for (String function : functionSet) {
            sb.append("|").append(function).append("\\(");
        }
        String formulaRule = sb.substring(1);
        Matcher matcher = Pattern.compile(formulaRule).matcher(cleanString);
        // opStack是udf栈，vStack是参数栈
        Stack<String> opStack = new Stack<>();
        Stack<String> vStack = new Stack<>();
        // baselineMap用于记录当前udf入栈时已经存在的参数数目，这意味着当程序运行至当前udf出栈时，vStack也必须出栈直到baselineMap记录的大小为止。
        // bracketMap用于记录当前udf层已经出现的左括号数目，每当出现一个右括号时该值减1，直到左括号数目为0时，这时出现的右括号才需要触发退栈计算的操作
        // tmpStringMap用于记录当前udf的临时字符串
        Map<Integer, Integer> baselineMap = new HashMap<>();
        Map<Integer, Integer> bracketMap = new HashMap<>();
        Map<Integer, String> tmpStringMap = new HashMap<>();
        // result用于记录下最终的结果
        String result = "";
        int index = 0;
        while (index < cleanString.length()) {
            // 四个index代表待处理的字符串中第一个匹配到指定字符串的下标
            int index1 = -1;
            int index2 = cleanString.substring(index).indexOf(",");
            int index3 = cleanString.substring(index).indexOf(")");
            int index4 = cleanString.substring(index).indexOf("(");
            if (matcher.find(index)) {
                index1 = matcher.start();
            }
            index1 = index1 == -1 ? Integer.MAX_VALUE : index1;
            index2 = index2 == -1 ? Integer.MAX_VALUE : index2 + index;
            index3 = index3 == -1 ? Integer.MAX_VALUE : index3 + index;
            index4 = index4 == -1 ? Integer.MAX_VALUE : index4 + index;
            int minIndex = Math.min(Math.min(Math.min(index1, index2), index3), index4);
            // 没有匹配到关键词，拼接保存的临时字符串后退出
            if (minIndex > cleanString.length()) {
                tmpStringMap.put(opStack.size(),
                    tmpStringMap.getOrDefault(opStack.size(), "") + cleanString.substring(index));
                break;
            }
            // 单数个引号的情况，此时关键词不作效，更新下标后后重新循环
            if (quotations[minIndex] % 2 == 1) {
                tmpStringMap.put(opStack.size(),
                    tmpStringMap.getOrDefault(opStack.size(), "") + cleanString
                        .substring(index, minIndex + 1));
                index = minIndex + 1;
                continue;
            }
            // 针对匹配到的四种情况进行处理
            if (minIndex == index1) {
                if (index1 > index) {
                    tmpStringMap.put(opStack.size(),
                        tmpStringMap.getOrDefault(opStack.size(), "") + cleanString
                            .substring(index, index1));
                }
                String function = matcher.group(0);
                opStack.push(function.substring(0, function.length() - 1));
                baselineMap.put(opStack.size(), vStack.size());
                index = matcher.end();
            } else if (minIndex == index2) {
                vStack.push("(" + tmpStringMap.getOrDefault(opStack.size(), "") + cleanString
                    .substring(index, index2) + ")");
                tmpStringMap.put(opStack.size(), "");
                index = index2 + 1;
            } else if (minIndex == index3) {
                int numOfBrackets = bracketMap.getOrDefault(opStack.size(), 0);
                if (numOfBrackets > 0) {
                    bracketMap.put(opStack.size(), numOfBrackets - 1);
                    tmpStringMap.put(opStack.size(),
                        tmpStringMap.getOrDefault(opStack.size(), "") + cleanString
                            .substring(index, index3 + 1));
                    index = index3 + 1;
                    continue;
                }
                if (index3 > index || tmpStringMap.getOrDefault(opStack.size(), "").length() > 0) {
                    vStack.push("(" + tmpStringMap.getOrDefault(opStack.size(), "") + cleanString
                        .substring(index, index3) + ")");
                    tmpStringMap.put(opStack.size(), "");
                }
                int num;
                String function;
                try {
                    num = baselineMap.get(opStack.size());
                    function = opStack.pop();
                } catch (Exception e) {
                    throw new FormulaException("括号不匹配！");
                }
                List<String> params = new ArrayList<>();
                while (vStack.size() > num) {
                    params.add(0, vStack.pop());
                }
                result =
                    tmpStringMap.getOrDefault(opStack.size(), "") + transferUdf(function, params,
                        sourceTable);
                tmpStringMap.put(opStack.size(), result);
                index = index3 + 1;
            } else {
                int numOfBrackets = bracketMap.getOrDefault(opStack.size(), 0);
                bracketMap.put(opStack.size(), numOfBrackets + 1);
                tmpStringMap.put(opStack.size(),
                    tmpStringMap.getOrDefault(opStack.size(), "") + cleanString
                        .substring(index, index4) + "(");
                index = index4 + 1;
            }
            if (opStack.size() > MAX_NESTING_LEVEL) {
                throw new FormulaException("公式嵌套层数过多！");
            }
        }
        if (bracketMap.getOrDefault(0, 0) != 0) {
            throw new FormulaException("括号不匹配！");
        }
        result = tmpStringMap.getOrDefault(opStack.size(), "");

        return replaceSpecialValue(result);
    }

    /**
     * 将udf转化为SQL合法字符串
     */
    public static String transferUdf(String function, List<String> params, String sourceTable) {
        String result;
        int numOfParams = params.size();
        switch (function) {
            case "sum":
                if (numOfParams == 0) {
                    throw new FormulaException("sum函数至少需要一个参数！");
                }
                result = String.join("+", params);
                break;
            case "average":
                if (numOfParams == 0) {
                    throw new FormulaException("average函数至少需要一个参数！");
                }
                result =
                    "((" + String.join("+", params) + ")/" + params.size() + "::numeric)::numeric";
                break;
            case "max":
                if (numOfParams == 0) {
                    throw new FormulaException("max函数至少需要一个参数！");
                }
                result = "greatest(" + String.join(",", params) + ")";
                break;
            case "min":
                if (numOfParams == 0) {
                    throw new FormulaException("min函数至少需要一个参数！");
                }
                result = "least(" + String.join(",", params) + ")";
                break;
            case "if":
                if (numOfParams != 3) {
                    throw new FormulaException("if函数需要三个参数！");
                }
                result =
                    "(case when " + params.get(0) + " then " + params.get(1) + " else " + params
                        .get(2) + " end)";
                break;
            case "number":
                if (numOfParams != 1) {
                    throw new FormulaException("number函数需要一个参数！");
                }
                result = "(" + params.get(0) + "::numeric)";
                break;
            case "power":
                if (numOfParams != 2) {
                    throw new FormulaException("power函数需要两个参数！");
                }
                result = "power(" + String.join(",", params) + ")";
                break;
            case "sqrt":
                if (numOfParams != 1) {
                    throw new FormulaException("sqrt函数需要一个参数！");
                }
                result = "sqrt(" + params.get(0) + ")";
                break;
            case "log":
                if (numOfParams != 1 && numOfParams != 2) {
                    throw new FormulaException("log函数需要一个或两个个参数！");
                }
                if (params.size() == 1) {
                    result = "log(Math.E," + params.get(0) + ")";
                } else {
                    result = "log(" + params.get(1) + "::numeric," + params.get(0) + ")";
                    break;
                }
                break;
            case "abs":
                if (numOfParams != 1) {
                    throw new FormulaException("abs函数需要一个参数！");
                }
                result = "abs(" + params.get(0) + ")";
                break;
            case "floor":
                if (numOfParams != 1) {
                    throw new FormulaException("floor函数需要一个参数！");
                }
                result = "floor(" + params.get(0) + ")";
                break;
            case "round":
                if (numOfParams != 1) {
                    throw new FormulaException("round函数需要一个参数！");
                }
                result = "round(" + params.get(0) + ")";
                break;
            case "ceiling":
                if (numOfParams != 1) {
                    throw new FormulaException("ceiling函数需要一个参数！");
                }
                result = "ceiling(" + params.get(0) + ")";
                break;
            case "int":
                if (numOfParams != 1) {
                    throw new FormulaException("int函数需要一个参数！");
                }
                result = params.get(0) + "::int";
                break;
            case "date":
                if (numOfParams != 0 && numOfParams != 1) {
                    throw new FormulaException("date函数需要零或一个参数！");
                }
                if (params.size() == 0) {
                    Date date = new Date();
                    result = "'" + date.toString() + "'::date";
                } else {
                    result = params.get(0) + "::date";
                }
                break;
            case "time":
                if (numOfParams != 0 && numOfParams != 1) {
                    throw new FormulaException("time函数需要零或一个参数！");
                }
                if (params.size() == 0) {
                    Timestamp now = new Timestamp(System.currentTimeMillis());
                    result = "'" + now.toString() + "'::timestamp";
                } else {
                    result = params.get(0) + "::timestamp";
                }
                break;
            case "now":
                if (numOfParams != 0) {
                    throw new FormulaException("now函数不需要参数！");
                }
                Timestamp now = new Timestamp(System.currentTimeMillis());
                result = "'" + now.toString() + "'::timestamp";
                break;
            case "timeadd":
                if (numOfParams != 3) {
                    throw new FormulaException("timeadd函数需要三个参数！");
                }
                String intervalString = "";
                String type = params.get(2);
                switch (type.substring(2, type.length() - 2)) {
                    case "year":
                    case "y":
                        intervalString = "interval '" + params.get(1) + " years'";
                        break;
                    case "month":
                    case "m":
                        intervalString = "interval '" + params.get(1) + " months'";
                        break;
                    case "day":
                    case "d":
                        intervalString = "interval '" + params.get(1) + " days'";
                        break;
                    case "hour":
                    case "h":
                        intervalString = "interval '" + params.get(1) + " hours'";
                        break;
                    case "minute":
                    case "min":
                        intervalString = "interval '" + params.get(1) + " minutes'";
                        break;
                    case "second":
                    case "s":
                        intervalString = "interval '" + params.get(1) + " seconds'";
                        break;
                    case "millisecond":
                    case "ms":
                        intervalString = "interval '" + params.get(1) + " milliseconds'";
                        break;
                }
                if (params.get(0).contains("current_timestamp")) {
                    result = params.get(0) + "+" + intervalString;
                } else {
                    // result = "TO_TIMESTAMP(" + params.get(0) + ",'yyyy-MM-dd hh24:mi:ss')" + "+" + intervalString;
                    result = "(" + params.get(0) + "::timestamp)" + "+" + intervalString;
                }
                break;
            case "string":
                if (numOfParams != 1) {
                    throw new FormulaException("string函数需要一个参数！");
                }
                result = params.get(0) + "::varchar";
                break;
            case "concat":
                result = "concat(" + String.join(",", params) + ")";
                break;
            case "replace":
                if (numOfParams != 3) {
                    throw new FormulaException("replace函数需要三个参数！");
                }
                result = "replace(" + String.join(",", params) + ")";
                break;
            case "aggr_max":
                if (numOfParams != 1) {
                    throw new FormulaException("aggr_max需要一个参数！");
                }
                result = "(select max(" + params.get(0) + ") from " + sourceTable + ")";
                break;
            case "aggr_min":
                if (numOfParams != 1) {
                    throw new FormulaException("aggr_min需要一个参数！");
                }
                result = "(select min(" + params.get(0) + ") from " + sourceTable + ")";
                break;
            case "aggr_average":
                if (numOfParams != 1) {
                    throw new FormulaException("aggr_average需要一个参数！");
                }
                result = "(select avg(" + params.get(0) + "::numeric) from " + sourceTable + ")";
                break;
            case "aggr_sum":
                if (numOfParams != 1) {
                    throw new FormulaException("aggr_sum需要一个参数！");
                }
                result = "(select sum(" + params.get(0) + "::numeric) from " + sourceTable + ")";
                break;
            default:
                throw new IllegalStateException("There is no " + function + " function!");
        }
        return result;
    }

    /**
     * 清洗输入的字符串及格式检查
     */
    public static String cleanInvalidString(String rawString) {
        return cleanInvalidString(rawString, functionSet);
    }

    /**
     * 清洗输入的字符串及格式检查
     */
    public static String cleanInvalidString(String rawString, Set<String> allowedFuncSet) {
        StringBuilder sb = new StringBuilder();
        if (rawString.length() >= 1024) {
            throw new FormulaException("公式长度超过限制！");
        }
        int currentQuota = 0;
        int[] quotaNumArray = new int[rawString.length()];
        for (int i = 0; i < rawString.length(); i++) {
            char c = rawString.charAt(i);
            if (c == '\'') {
                if (i == 0 || rawString.charAt(i - 1) != '\\') {
                    currentQuota++;
                }
                sb.append(c);
            } else {
                if ((currentQuota & 1) == 0) {
                    // 全角转半角
                    if (c == 12288) {
                        sb.append((char) 32);
                        continue;
                    } else if (c > 65280 && c < 65375) {
                        sb.append((char) (c - 65248));
                        continue;
                    }
                    Pattern p2 = Pattern.compile(
                        "^\\w|\\.|\\(|\\)|\\$|\\|'|,|\\+|-|\\*|%|>|<|=|\\^|!|&|/|\\s|\\t|\\r|\\n$");
                    Matcher m2 = p2.matcher(String.valueOf(c));
                    if (!m2.matches()) {
                        throw new FormulaException("公式中存在非法字符！");
                    }
                    Pattern p1 = Pattern.compile("\\s*|\\t|\\r|\\n");
                    Matcher m1 = p1.matcher(String.valueOf(c));
                    if (!m1.matches()) {
                        sb.append(c);
                    }
                } else {
                    sb.append(c);
                }
            }
            quotaNumArray[sb.length() - 1] = currentQuota;
        }
        String s1 = sb.toString();
        if ((currentQuota & 1) == 1) {
            throw new FormulaException("公式中引号个数不匹配！");
        }
        // 匹配字母下划线和小数点，找出udf和special constant
        Pattern p2 = Pattern.compile("[a-zA-Z_.]+");
        Matcher m2 = p2.matcher(s1);
        int index = 0;
        while (index < s1.length()) {
            if (!m2.find(index)) {
                break;
            }
            String tmpUdf = m2.group(0);
            index = m2.start();
            if (index == s1.length()) {
                break;
            }
            if ((quotaNumArray[index] & 1) == 1 || tmpUdf.equals(".")) {
                index = m2.end();
                continue;
            }
            if (!allowedFuncSet.contains(tmpUdf) && !specialStrings.contains(tmpUdf) && !tmpUdf
                .equals("c")) {
                if (functionSet.contains(tmpUdf)){
                    throw new InvalidFormulaException("当前不支持此函数添加公式，您可尝试聚合类的函数, 例如: aggr_" + tmpUdf);
                }
                throw new FormulaException("非法函数：" + tmpUdf);
            }
            index = m2.end();
            if (allowedFuncSet.contains(tmpUdf) && s1.charAt(index) != '(') {
                throw new FormulaException("函数后必须紧跟\"(\"");
            }
        }
        int leftBrace = 0, rightBrace = 0;
        for (int i = 0; i < s1.length(); i++) {
            if ((quotaNumArray[i] & 1) == 0) {
                if (s1.charAt(i) == '(') {
                    leftBrace++;
                } else if (s1.charAt(i) == ')') {
                    rightBrace++;
                }
            }
        }
        if (leftBrace != rightBrace) {
            throw new FormulaException("括号不匹配！");
        }
        // 匹配列变量
        Pattern p3 = Pattern.compile("\\$[a-zA-Z0-9]+");
        Matcher m3 = p3.matcher(s1);
        index = 0;
        while (index < s1.length()) {
            if (!m3.find(index)) {
                break;
            }
            String tmpColumnString = m3.group(0);
            if (tmpColumnString.length() < 3 || tmpColumnString.charAt(1) != 'c') {
                throw new FormulaException("列变量格式错误！");
            }
            String columnIndex = tmpColumnString.substring(2);
            int value;
            try {
                value = Integer.parseInt(columnIndex);
            } catch (Exception e) {
                throw new FormulaException("列变量格式错误！");
            }
            if (value <= 0) {
                throw new FormulaException("列变量下标必须为正整数！");
            }
            index = m3.end();
        }

        return s1;
    }

    private String actionNumberFormatConversion() {
//        String subAction = data.getString("subAction");
//        String col = data.getString("col");
//        List<String> cols = Lists.newLinkedList(columnTypes.keySet());
//        if (!cols.contains(col)){
//            return String.format("error: column %s does not exist", col);
//        }
//        int index = cols.indexOf(col);
//        cols.remove(index);
//        cols = SqlUtil.formatPGSqlCols(cols);
//
//        String newCol = "";
//        int digit = data.getInteger("digit");
//        switch (subAction) {
//            case "%":
//                newCol = "round("+SqlUtil.formatPGSqlColName(col)+"*100,"+digit+")||'%' "+SqlUtil.formatPGSqlColName(col);
//                break;
//            case "E-NOTATION":
//                newCol = "pipeline.sys_func_format_e_notation("+SqlUtil.formatPGSqlColName(col)+","+digit+") "+SqlUtil.formatPGSqlColName(col);
//        }
//        cols.add(index, newCol);
//        String selectSql = buildSelectSql(cols.iterator(), sourceTable);
        //该功能由前端处理
        String selectSql = String.format(SELECT_SQL, "*", sourceTable);

        logger.info("SQL generated from actionPartitionAndOrder : {}", selectSql);
        return String.format(SqlTemplate.CREATE_VIEW_SQL, targetTable, selectSql);
    }

    /**
     * 测试新增列
     */
    private static String testAddColumn(String rawString) {
        List<String> cols = new ArrayList<String>() {
            {

                add("科目");
                add("班级");
                add("分数");
                add("序号");

            }
        };
        return getWidgetFormulaSelectSql2(cols,"pipeline.view_tclean_42146_1640162875607", rawString );
    }

    public static void main(String[] args) {

//        String[] testStrings = new String[]{
//            "replace('as,db','))))s','Math.E')",
//            "number('123')",
//            "string(date('2019-05-02 23:00:01.023'))",
//            "replace($c3,'a','e')",
//            "timeadd(time('2021-05-19 14:53:21.012'),2,'h')",
//            "number(123)",
//            "timeadd('2021-01-01 00:00:00',2,'day')",
//            "if($c4>$c5,200,if(2>3,5,6))",
//            "true&1",
//            "average($c4,$c5)+2",
//            "timeadd(now(),2,'hour')",
//            "max(3,Math.E)",
//            "concat('123',456)",
//            "aggr_average($c4)+aggr_min($c4)",
//            "aggr_max($c4)+$c5*2",
//            "Math.PI*2",
//            "max(2,(3+3))+1",
//            "$c4*($c5-2)",
//            "$c5+3",
//            "$c4*$c5",
//            "$c4/10",
//            "$c4*4+5*$c5",
//            "sum($c4,$c5,10)-10",
//            "if($c4>$c5,1,2)",
//            "if(2>3,$c4,$c5)+3",
//            "$c4^3-$c5^2",
//            "number(2.4)+$c4",
//            "average($c4,$c5)+3"
//        };

        String[] aggrStrings = new String[]{
                "aggr_sum($c4)",
                "aggr_sum($c3)",
                "aggr_average($c4)",
                "aggr_average($c3)",
                "aggr_min($c4)",
                "aggr_min($c3)",
                "aggr_max($c4)",
                "aggr_max($c3)",
        };

        for (String s : aggrStrings) {
            System.out.println(testAddColumn(s));
        }
    }
}